/*
 * GenerateSql.java		1.00	2008. 01. 21
 *
 * Copyright (c) 2008 David.Kim(KJS). All Rights Reserved.
 * 
 * This software is the confidential and proprietary information
 * of David.Kim(KJS).  You shall not disclose such Confidential Information
 * and shall use it only in accordance with the terms of the license agreement
 * you entered into with David.Kim(KJS).
 */
package doit.gen.util;

import java.io.File;
import java.io.RandomAccessFile;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import doit.framework.util.Config;
import doit.gen.domain.Column;

/**
 * <pre>
 * system      : 공통
 * menu        : 공통
 * source      : GenerateSql.java
 * description : 테이블에 매핑 되는 Query XML 생성
 * </pre> 
 * @version
 * <pre> 
 * 1.0	2008. 01. 21  leewan  최초 작성
 * 1.1	
 * </pre>
 */

@SuppressWarnings("unchecked")
public class GenerateIBatis implements BaseGenerate {

	private String auth = "";
	
	public void generate( Map input, String tableName, List columns) throws Exception {
		String packageName = (String) input.get("packageName");
		String auth = (String) input.get("auth");
		String basePath = Config.getString("doit.generator.path.base");
        String parentPath = basePath + "/" + packageName;
		String childPath = Config.getString("doit.generator.path.sql");
		
		if (auth.equals("usr"))
			this.auth = auth;
		
		// 폴더 생성
		GeneratorUtil.makeDirectorys( basePath, packageName);
		
		// 폴더 생성
		GeneratorUtil.makeDirectorys( parentPath, childPath);

		// 클래스명 분석
		String className = GeneratorUtil.getClassName( tableName);
		
		// 내용 생성
		StringBuffer sb = new StringBuffer();
		
		// 클래스 시작
		sb.append( printClassStart(className));
		
		// 클래스
		sb.append( printClass(className, columns));
		
		// 파일 생성
		File file = GeneratorUtil.makeFile( parentPath + "/" + childPath + "/" + className + ".xml");
		
		// 파일에 쓰기
		RandomAccessFile raf = new RandomAccessFile( file, "rw");
		GeneratorUtil.write( raf, sb.toString());
		
		// 파일 닫기
		raf.close();
	}

    public String printImport(String packageName, String className) {
        return "";
    }	
	
	public String printClass(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append("<sqlMap namespace=\"" + auth + className + "\">").append("\n\n");
		sb.append(getListCondition());
		sb.append(getPageList(className, columns));
		sb.append(getList(className, columns));
		sb.append(getPageListCount(className, columns));
		sb.append(get(className, columns));
		sb.append(insert(className, columns));
		sb.append(update(className, columns));
		sb.append(updateField(className, columns));
		sb.append(delete(className, columns));
		sb.append(deleteAll(className, columns));
		sb.append(exist(className, columns));
		sb.append("</sqlMap>").append("\n\n");
		return sb.toString();
	}
	
	public String getListCondition() throws Exception {
	    StringBuffer sb = new StringBuffer();
	    sb.append("    <sql id=\"listCondition\">").append("\n");
	    sb.append("    </sql>").append("\n");
	    return sb.toString();
	}
	
	public String getPageList(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <select id=\"get" + className + "PageList\" resultClass=\"HashMap\">").append("\n");
		sb.append("        SELECT * ").append("\n");
		sb.append("        FROM").append("\n");
		sb.append("               (").append("\n");
		sb.append("                SELECT TEMP.*, ").append("\n");
		sb.append("                <isNotEmpty property=\"rowsPerPage\">").append("\n");
		sb.append("                       CEIL(ROWNUM / #rowsPerPage#) AS \"page\" ").append("\n");
		sb.append("                </isNotEmpty>").append("\n");
		sb.append("                <isEmpty property=\"rowsPerPage\">").append("\n");
		sb.append("                       CEIL(ROWNUM / 10) AS \"page\" ").append("\n");
		sb.append("                </isEmpty>").append("\n");
		sb.append("                FROM").append("\n");
		sb.append("                      (").append("\n");
		sb.append("                       SELECT \n");
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (i + 1 < columns.size())
				sb.append("	        	              " + column.getColumnName() + " AS " + "\"" + GeneratorUtil.getFieldName( column.getColumnName()) + "\"" + ",").append("\n");
			else 
				sb.append("	        	              " + column.getColumnName() + " AS " + "\"" + GeneratorUtil.getFieldName( column.getColumnName()) + "\"").append("\n" );
		} 
		sb.append("        		       FROM   " + tableName).append("\n");
		sb.append("        		       WHERE  1=1").append("\n");
		sb.append("        		       <include refid=\"listCondition\" />").append("\n");
		sb.append("        		       <isNotEmpty property=\"orderBy\">").append("\n");
		sb.append("        			       $orderBy$").append("\n");
		sb.append("        		       </isNotEmpty>").append("\n");
		sb.append("        	          ) TEMP ").append("\n");
		sb.append("               )").append("\n");
		sb.append("        <isNotEmpty property=\"page\">").append("\n");
		sb.append("        WHERE \"page\" = #page#").append("\n");
		sb.append("        </isNotEmpty>").append("\n");
		sb.append("        <isEmpty property=\"page\">").append("\n");
		sb.append("        WHERE \"page\" = 1").append("\n");
		sb.append("        </isEmpty>").append("\n");
		sb.append("    </select>").append("\n\n");
		return sb.toString();
	}
	
	public String getPageListCount(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <select id=\"get" + className + "PageListCount\" resultClass=\"long\">").append("\n");
		sb.append("        SELECT COUNT(ROWNUM) AS \"count\"").append("\n");
		sb.append("        FROM   " + tableName).append("\n");
		sb.append("        WHERE  1=1").append("\n");
		sb.append("        <include refid=\"listCondition\" />").append("\n");
		sb.append("    </select>").append("\n\n");
		return sb.toString();
	}	
	
	public String getList(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <select id=\"get" + className + "List\" resultClass=\"HashMap\">").append("\n");
		sb.append("        SELECT ").append("\n");
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (i+ 1 < columns.size())
				sb.append("               " + column.getColumnName() + " AS " + "\"" + GeneratorUtil.getFieldName( column.getColumnName()) + "\"" + ",").append("\n");
			else 
				sb.append("               " + column.getColumnName() + " AS " + "\"" + GeneratorUtil.getFieldName( column.getColumnName()) + "\"").append("\n");
		} 
		sb.append("        FROM   " + tableName).append("\n");
		sb.append("        WHERE  1=1").append("\n");
        sb.append("        <include refid=\"listCondition\" />").append("\n");
		sb.append("    </select>").append("\n\n");
		return sb.toString();
	}
	
	public String get(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <select id=\"get" + className + "\" resultClass=\"HashMap\">").append("\n");
		sb.append("        SELECT ").append("\n");
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (i+ 1 < columns.size())
				sb.append("               " + column.getColumnName() + " AS " + "\"" + GeneratorUtil.getFieldName( column.getColumnName()) + "\"" + ",").append("\n");
			else 
				sb.append("               " + column.getColumnName() + " AS " + "\"" + GeneratorUtil.getFieldName( column.getColumnName()) + "\"").append("\n");
		} 
		sb.append("        FROM   " + tableName).append("\n");
		sb.append("        WHERE  1=1").append("\n");
		int count = 0;
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (column.getPrimaryYn().equals("Y")) {
				sb.append("        AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
				count++;
			}
		}
		sb.append("    </select>").append("\n\n");
		return sb.toString();
	}
	
	public String insert(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		
		List pkList = GeneratorUtil.getPkList(columns);
		String lastPkColumnName = GeneratorUtil.getlastPkColumn(columns).getColumnName();
		String lastPkDataType = GeneratorUtil.getlastPkColumn(columns).getDataType();
		String resultClass = "";
		String selectKeyQuery = "";
		if( "NUMBER".equals( lastPkDataType)) {
		    resultClass = "long";
		    selectKeyQuery = "NVL(MAX("+lastPkColumnName+")+1, 1)";
		}
		else if( "VARCHAR".equals( lastPkDataType) || "VARCHAR2".equals( lastPkDataType)) {
		    resultClass = "String";
		    selectKeyQuery = "NVL(MAX(TO_NUMBER("+lastPkColumnName+"))+1, 1)";
		}
		
		sb.append("    <insert id=\"insert" + className + "\">").append("\n");
		sb.append("        <selectKey resultClass=\""+resultClass+"\" keyProperty=\""+ GeneratorUtil.getFieldName( lastPkColumnName) +"\">").append("\n");
		sb.append("            SELECT "+selectKeyQuery+" AS \"selectKey\"").append("\n");
		sb.append("            FROM   "+tableName).append("\n");
		sb.append("            WHERE  1=1").append("\n");
		int count = 0;
		for (int i = 0 ; i < pkList.size()-1 ; i++) {
			Column column = (Column)columns.get(i);
			sb.append("            AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
			count++;
		}	
		sb.append("        </selectKey>").append("\n");
		
		sb.append("        INSERT INTO " + tableName).append("\n");
		sb.append("        (").append("\n");
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (i+ 1 < columns.size())
				sb.append("            " + column.getColumnName() + ",").append("\n");
			else 
				sb.append("            " + column.getColumnName()).append("\n");
		} 
		sb.append("        )").append("\n");
		sb.append("        VALUES").append("\n");
		sb.append("        (").append("\n");
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			
			if (i + 1 < columns.size()) {
			    if (column.getColumnName().equals( Config.getString("doit.generator.db.creDt")) || column.getColumnName().equals( Config.getString("doit.generator.db.updDt")))
					sb.append("            SYSDATE,").append("\n");
				else
				    if(column.getDataType().equals("DATE"))
				        sb.append("            TO_DATE(#" + GeneratorUtil.getFieldName(column.getColumnName()) + "#, 'YYYY-MM-DD HH24:MI:SS'),").append("\n");
				    else
				        sb.append("            #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#,").append("\n");
			}
			else {
			    if (column.getColumnName().equals( Config.getString("doit.generator.db.creDt")) || column.getColumnName().equals( Config.getString("doit.generator.db.updDt")))
					sb.append("            SYSDATE").append("\n");
				else
				    if(column.getDataType().equals("DATE"))
				        sb.append("            TO_DATE(#" + GeneratorUtil.getFieldName(column.getColumnName()) + "#, 'YYYY-MM-DD HH24:MI:SS')").append("\n");
				    else
				        sb.append("            #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
			}
		} 
		sb.append("        )").append("\n");
		sb.append("    </insert>").append("\n\n");
		return sb.toString();
	}
	
	public String update(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <update id=\"update" + className + "\">").append("\n");
		sb.append("        UPDATE " + tableName).append("\n");
		sb.append("        SET    ").append("\n");
		int count = 0;

		List updColumns = new ArrayList();
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if( column.getPrimaryYn().equals( "")
					&& !column.getColumnName().equals( Config.getString("doit.generator.db.creId")) 
					&& !column.getColumnName().equals( Config.getString("doit.generator.db.creClass"))
					&& !column.getColumnName().equals( Config.getString("doit.generator.db.creIp"))
					&& !column.getColumnName().equals( Config.getString("doit.generator.db.creDt")))
			{
				updColumns.add( column);
			}
		}
		
		for (int i = 0 ; i < updColumns.size() ; i++) {
			Column column = (Column)updColumns.get(i);
			if( column.getPrimaryYn().equals( "")
			        && !column.getColumnName().equals( Config.getString("doit.generator.db.creId")) 
                    && !column.getColumnName().equals( Config.getString("doit.generator.db.creClass"))
                    && !column.getColumnName().equals( Config.getString("doit.generator.db.creIp"))
                    && !column.getColumnName().equals( Config.getString("doit.generator.db.creDt")))
			{
				if (i+1 < updColumns.size()) {
					if( column.getColumnName().equals( Config.getString("doit.generator.db.updDt")))
						sb.append("               " + column.getColumnName() + " = SYSDATE,").append("\n");
					else
					    if(column.getDataType().equals("DATE"))
					        sb.append("               " + column.getColumnName() + " = TO_DATE(#" + GeneratorUtil.getFieldName(column.getColumnName()) + "#, 'YYYY-MM-DD HH24:MI:SS'),").append("\n");
					    else
					        sb.append("               " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#,").append("\n");
				}
				else { 
					if( column.getColumnName().equals( Config.getString("doit.generator.db.updDt")))
						sb.append("               " + column.getColumnName() + " = SYSDATE").append("\n");
					else
					    if(column.getDataType().equals("DATE"))
					        sb.append("               " + column.getColumnName() + " = TO_DATE(#" + GeneratorUtil.getFieldName(column.getColumnName()) + "#, 'YYYY-MM-DD HH24:MI:SS')").append("\n");
					    else
					        sb.append("               " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
				}
				count++;
			}
		}
		sb.append("        WHERE  1=1").append("\n");
		count = 0;
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (column.getPrimaryYn().equals("Y")) {
				sb.append("        AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
				count++;
			}
		}	
		sb.append("    </update>").append("\n\n");
		return sb.toString();
	}

	public String updateField(String className, List columns) throws Exception {
	    StringBuffer sb = new StringBuffer();
	    String tableName = ((Column)columns.get(0)).getTableName();
	    sb.append("    <update id=\"updateField" + className + "\">").append("\n");
	    sb.append("        UPDATE " + tableName).append("\n");
	    sb.append("        <dynamic prepend=\"SET\">").append("\n");
	    int count = 0;
	    
	    List updColumns = new ArrayList();
	    for (int i = 0 ; i < columns.size() ; i++) {
	        Column column = (Column)columns.get(i);
	        if( column.getPrimaryYn().equals( "")
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creId")) 
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creClass"))
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creIp"))
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creDt")))
	        {
	            updColumns.add( column);
	        }
	    }
	    
	    for (int i = 0 ; i < updColumns.size() ; i++) {
	        Column column = (Column)updColumns.get(i);
	        if( column.getPrimaryYn().equals( "")
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creId")) 
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creClass"))
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creIp"))
	                && !column.getColumnName().equals( Config.getString("doit.generator.db.creDt")))
	        {

                if( column.getColumnName().equals( Config.getString("doit.generator.db.updDt"))) {
                    sb.append("               <isNotEmpty property=\""+ GeneratorUtil.getFieldName(column.getColumnName()) +"\" prepend=\",\">").append("\n");
                    sb.append("               " + column.getColumnName() + " = SYSDATE").append("\n");
                    sb.append("               </isNotEmpty>").append("\n");
                }
                else {
                    if(column.getDataType().equals("DATE")) {
                        sb.append("               <isNotEmpty property=\""+ GeneratorUtil.getFieldName(column.getColumnName()) +"\" prepend=\",\">").append("\n");
                        sb.append("               " + column.getColumnName() + " = TO_DATE(#" + GeneratorUtil.getFieldName(column.getColumnName()) + "#, 'YYYY-MM-DD HH24:MI:SS')").append("\n");
                        sb.append("               </isNotEmpty>").append("\n");
                    }
                    else {
                        sb.append("               <isNotEmpty property=\""+ GeneratorUtil.getFieldName(column.getColumnName()) +"\" prepend=\",\">").append("\n");
                        sb.append("               " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
                        sb.append("               </isNotEmpty>").append("\n");
                    }
                }
	            count++;
	        }
	    }
	    sb.append("        </dynamic>").append("\n");
	    sb.append("        WHERE  1=1").append("\n");
	    count = 0;
	    for (int i = 0 ; i < columns.size() ; i++) {
	        Column column = (Column)columns.get(i);
	        if (column.getPrimaryYn().equals("Y")) {
	            sb.append("        AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
	            count++;
	        }
	    }	
	    sb.append("    </update>").append("\n\n");
	    return sb.toString();
	}

	public String delete(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <delete id=\"delete" + className + "\">").append("\n");
		sb.append("        DELETE FROM " + tableName).append("\n");
		sb.append("        WHERE  1=1").append("\n");
		int count = 0;
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (column.getPrimaryYn().equals("Y")) {
				sb.append("        AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
				count++;
			}
		}	
		sb.append("    </delete>").append("\n\n");
		return sb.toString();
	}

	public String deleteAll(String className, List columns) throws Exception {
        List pkList = GeneratorUtil.getPkList(columns);
        String lastPkColumnName = GeneratorUtil.getlastPkColumn(columns).getColumnName();
		
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <delete id=\"delete" + className + "All\">").append("\n");
		sb.append("        DELETE FROM " + tableName).append("\n");
		sb.append("        WHERE  1=1").append("\n");
		int count = 0;
		for (int i = 0 ; i < pkList.size()-1 ; i++) {
			Column column = (Column)pkList.get(i);
			sb.append("        AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
			count++;
		}

		if( pkList != null && pkList.size() > 0)
		{
			sb.append( "        <dynamic prepend=\"WHERE " + lastPkColumnName + " IN \">").append("\n");
			sb.append( "            <isNotEmpty property=\"" + GeneratorUtil.getFieldName(lastPkColumnName) + "\">").append("\n");
			sb.append( "                <iterate property=\"" + GeneratorUtil.getFieldName(lastPkColumnName) + "\" open=\"(\" close=\")\" conjunction=\",\">").append("\n");
			sb.append( "                    #" + GeneratorUtil.getFieldName(lastPkColumnName) + "[]#").append("\n");
			sb.append( "                </iterate>").append("\n");
			sb.append( "            </isNotEmpty>").append("\n");
			sb.append( "            <isEmpty property=\"" + GeneratorUtil.getFieldName(lastPkColumnName) + "\">").append("\n");
			sb.append( "                ('')").append("\n");
			sb.append( "            </isEmpty>").append("\n");
			sb.append( "        </dynamic>").append("\n");
		}
		else
		{
			sb.append( "        <dynamic prepend=\"WHERE  userdefine IN \">").append("\n");
			sb.append( "            <isNotEmpty property=\"userdefine\">").append("\n");
			sb.append( "                <iterate property=\"userdefine\" open=\"(\" close=\")\" conjunction=\",\">").append("\n");
			sb.append( "                    #userdefine[]#").append("\n");
			sb.append( "                </iterate>").append("\n");
			sb.append( "            </isNotEmpty>").append("\n");
			sb.append( "            <isEmpty property=\"userdefine\">").append("\n");
			sb.append( "                ('')").append("\n");
			sb.append( "            </isEmpty>").append("\n");
			sb.append( "        </dynamic>").append("\n");
		}
		sb.append("    </delete>").append("\n\n");
		return sb.toString();
	}
	
	public String exist(String className, List columns) throws Exception {
		StringBuffer sb = new StringBuffer();
		String tableName = ((Column)columns.get(0)).getTableName();
		sb.append("    <select id=\"exist" + className + "\" resultClass=\"long\">").append("\n");
		sb.append("        SELECT COUNT(ROWNUM) AS \"exist\"").append("\n");
		sb.append("        FROM   " + tableName).append("\n");
		sb.append("        WHERE  1=1").append("\n");
		int count = 0;
		for (int i = 0 ; i < columns.size() ; i++) {
			Column column = (Column)columns.get(i);
			if (column.getPrimaryYn().equals("Y")) {
				sb.append("        AND    " + column.getColumnName() + " = #" + GeneratorUtil.getFieldName(column.getColumnName()) + "#").append("\n");
				count++;
			}
		}
		sb.append("    </select>").append("\n\n");
		return sb.toString();
	}

	public String printClassEnd() {
	    return "";
	}

	public String printClassStart(String className) {
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>").append("\n");
		sb.append("<!DOCTYPE sqlMap PUBLIC \"-//ibatis.apache.org//DTD SQL Map 2.0//EN\" \"http://ibatis.apache.org/dtd/sql-map-2.dtd\">").append("\n");
		return sb.toString();
	}

	public String printField(Column column) throws Exception {
	    return "";
	}

	public String emptyString(String value, int length) {
		String _ret = "    ";
		for (int i = 0 ; i < length - value.length() ; i++) {
			_ret+= " ";
		}
		return _ret;	
	}
}
